package com.api.gbhg.liuwenbo.dao;

import cn.hutool.core.util.StrUtil;
import com.api.gbhg.liuwenbo.pojo.TreeStucture;
import com.api.gbhg.liuwenbo.utils.Utils;
import com.api.gbhg.zengping.dao.PropertyManagementCostDao;
import com.google.common.base.Joiner;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import weaver.conn.RecordSet;
import weaver.conn.RecordSetTrans;
import weaver.general.Util;

import java.math.BigDecimal;
import java.util.*;

public class TreeStuctureDBUtil {


    private static final Logger LOGGER = LoggerFactory.getLogger("gbhg");

    //---------------------------------------------------------------------------获取科目代码

    /**
     * 根据id获取功能科目代码
     * @param id
     * @return
     */
    public static String getGnkmDm(Integer id){
        RecordSet rs = new RecordSet();
        String sql = " select kmdm from uf_gnkm where id = "+id+" ";
        rs.executeQuery(sql);
        rs.next();
        return rs.getString("kmdm");
    }

    //----------------------------------------------------------------------------------------------添加树结构

    /**
     * 将下级的金额添加到一级Sql
     * @param szgnkm
     * @param param
     * @return
     */
    public static String AddKmJeSql(Integer szgnkm,Map<String,Object> param){
        StringBuffer sb = new StringBuffer();

        sb.append(" update uf_zyczbksr_km set ");
        if (param.containsKey("yjsr"))
            sb.append("yjsr = yjsr+"+param.get("yjsr")+",yjsrsy = yjsrsy + "+param.get("yjsr")+",");
        if (param.containsKey("yqndyjjzje"))
            sb.append("yqndyjjzje = yqndyjjzje+"+param.get("yqndyjjzje")+",yqndyjjzsyje = yqndyjjzsyje +"+param.get("yqndyjjzje")+",");
        if (param.containsKey("yqndsjjzje"))
            sb.append("yqndsjjzje = yqndsjjzje+"+param.get("yqndsjjzje")+",yqndsjjzsyje = yqndsjjzsyje+"+param.get("yqndsjjzje")+",jzsyje = jzsyje + "+param.get("yqndsjjzje")+",");


        return sb.substring(0,sb.length() - 1).toString() + " where id = " + szgnkm + " ";
//        RecordSet rs = new RecordSet();
//        String sql = " select sum(yjsr) yjsr,sum(yqndyjjzje) yqndyjjzje,sum(yqndsjjzje) yqndsjjzje from uf_zyczbksr_xj where szgnkm = "+szgnkm+" and sjzxsr is null ";
//        rs.executeQuery(sql);
//        rs.next();
//        String yjsr = !rs.getString("yjsr").equals("")?rs.getString("yjsr"):"0";
//        String yqndyjjzje = !rs.getString("yqndyjjzje").equals("")?rs.getString("yqndyjjzje"):"0";
//        String yqndsjjzje = !rs.getString("yqndsjjzje").equals("")?rs.getString("yqndsjjzje"):"0";
//        String upSql = " update uf_zyczbksr_km set yjsr = "+yjsr+",yqndyjjzje = "+yqndyjjzje+",yqndsjjzje = "+yqndsjjzje+"," +
//                " yjsrsy = "+yjsr+",yqndyjjzsyje = "+yqndyjjzje+",yqndsjjzsyje = "+yqndsjjzje+" where id = "+szgnkm+" ";
//        return upSql;
    }

    /**
     * add 11.30
     * 将下级的金额添加到一级Sql
     * @param szgnkm
     * @param param
     * @version 2.0
     * @return
     */
    public static String AddKmJeSql2(Integer szgnkm,Map<String,Object> param){
        RecordSet rs = new RecordSet();
        String sql = " select sum(yjsr) yjsr,sum(yqndyjjzje) yqndyjjzje,sum(yqndsjjzje) yqndsjjzje,sum(yjsrsy) yjsrsy," +
                "sum(yqndyjjzsyje) yqndyjjzsyje,sum(yqndsjjzsyje) yqndsjjzsyje," +
                "sum(yhpje) yhpje,sum(djje) djje,sum(sjbxje) sjbxje,sum(yhpsy) yhpsy,sum(yqndyjjzyyje) yqndyjjzyyje," +
                "sum(jzyyje) jzyyje,sum(jzsyje) jzsyje,sum(yqndsjjzyyje) yqndsjjzyyje,sum(snjzje) snjzje,sum(snjzhpje) snjzhpje," +
                "sum(snjzhpbxje) snjzhpbxje,sum(snjzhpsyje) snjzhpsyje,sum(snjzhpdjje) snjzhpdjje from uf_zyczbksr_xj where szgnkm = "+szgnkm+" and sjzxsr is null ";
        rs.executeQuery(sql);
        rs.next();
        String yjsr = !rs.getString("yjsr").equals("")?rs.getString("yjsr"):"0";
        String yqndyjjzje = !rs.getString("yqndyjjzje").equals("")?rs.getString("yqndyjjzje"):"0";
        String yqndsjjzje = !rs.getString("yqndsjjzje").equals("")?rs.getString("yqndsjjzje"):"0";
        String yjsrsy = rs.getString("yjsrsy");
        String yqndyjjzsyje = rs.getString("yqndyjjzsyje");
        String yqndsjjzsyje = rs.getString("yqndsjjzsyje");
        String yhpje = Util.null2String(rs.getString("yhpje")).equals("")?"0":rs.getString("yhpje");
        String djje = Util.null2String(rs.getString("djje")).equals("")?"0":rs.getString("djje");
        String sjbxje = Util.null2String(rs.getString("sjbxje")).equals("")?"0":rs.getString("sjbxje");
        String yhpsy = Util.null2String(rs.getString("yhpsy")).equals("")?"0":rs.getString("yhpsy");
        String yqndyjjzyyje = Util.null2String(rs.getString("yqndyjjzyyje")).equals("")?"0":rs.getString("yqndyjjzyyje");
        String jzyyje = Util.null2String(rs.getString("jzyyje")).equals("")?"0":rs.getString("jzyyje");
        String jzsyje = Util.null2String(rs.getString("jzsyje")).equals("")?"0":rs.getString("jzsyje");
        String yqndsjjzyyje = Util.null2String(rs.getString("yqndsjjzyyje")).equals("")?"0":rs.getString("yqndsjjzyyje");
        String snjzje = Util.null2String(rs.getString("snjzje")).equals("")?"0":rs.getString("snjzje");
        String snjzhpje = Util.null2String(rs.getString("snjzhpje")).equals("")?"0":rs.getString("snjzhpje");
        String snjzhpbxje = Util.null2String(rs.getString("snjzhpbxje")).equals("")?"0":rs.getString("snjzhpbxje");
        String snjzhpsyje = Util.null2String(rs.getString("snjzhpsyje")).equals("")?"0":rs.getString("snjzhpsyje");
        String snjzhpdjje = Util.null2String(rs.getString("snjzhpdjje")).equals("")?"0":rs.getString("snjzhpdjje");

        String upSql = " update uf_zyczbksr_km set yjsr = "+yjsr+",yqndyjjzje = "+yqndyjjzje+",yqndsjjzje = "+yqndsjjzje+"," +
                " yjsrsy = "+yjsrsy+",yqndyjjzsyje = "+yqndyjjzsyje+",yqndsjjzsyje = "+yqndsjjzsyje+"," +
                " yhpje = "+yhpje+",djje = "+djje+",sjbxje = "+sjbxje+",hpsyje = "+yhpsy+",yqndyjjzyyje = "+yqndyjjzyyje+"," +
                "jzyyje = "+jzyyje+",jzsyje = "+jzsyje+",yqndsjjzyyje = "+yqndsjjzyyje+",snjzje = "+snjzje+",snjzhpje = "+snjzhpje+"," +
                "snjzhpbxje = "+snjzhpbxje+",snjzhpsyje = "+snjzhpsyje+",snjzhpdjje = "+snjzhpdjje+"  where id = "+szgnkm+"  ";
        return upSql;
    }

    /**
     * 将下级的金额添加到上级级Sql
     * @param superiorId
     * @param param
     * @return
     */
    public static String AddxjJeSql(List<String> superiorId,Map<String,Object> param,String SumSql){


//        StringBuffer sb = new StringBuffer();
//        sb.append(" update uf_zyczbksr_xj set ");
//        if (param.containsKey("yjsr"))
//            sb.append("yjsr = yjsr+"+param.get("yjsr")+",yjsrsy = yjsrsy +"+param.get("yjsr")+",");
//        if (param.containsKey("yqndyjjzje"))
//            sb.append("yqndyjjzje = yqndyjjzje+"+param.get("yqndyjjzje")+",");
//        if (param.containsKey("yqndsjjzje"))
//            sb.append("yqndsjjzje = yqndsjjzje+"+param.get("yqndsjjzje")+",jzsyje = jzsyje+"+param.get("yqndsjjzje")+",");
//
//        return sb.substring(0,sb.length() - 1).toString() + " where id in ("+Joiner.on(",").join((Iterable<?>) superiorId)+") ";


        String SumSql2 = TreeStuctureDBUtil.getbottomSumJe(param.get("sjzxsr").toString());
        RecordSet rs = new RecordSet();
        rs.executeQuery(SumSql);
        rs.next();
        String yjsr = rs.getString("yjsr");
        String yqndyjjzje = rs.getString("yqndyjjzje");
        String yqndsjjzje = rs.getString("yqndsjjzje");
        String upSql = " update uf_zyczbksr_xj set yjsr = "+yjsr+",yqndyjjzje = "+yqndyjjzje+",yqndsjjzje = "+yqndsjjzje+"," +
                " yjsrsy = "+yjsr+",jzsyje = "+yqndsjjzje+" where id in ("+Joiner.on(",").join((Iterable<?>) superiorId)+") ";

        return upSql;
    }

    /**
     * 同步上层
     * @param id
     * @return
     */
    public static boolean SynUpperStrata(String id){
        RecordSetTrans rst = new RecordSetTrans();
        rst.setAutoCommit(false);
        String sql = " select sum(yjsr) yjsr,sum(yqndyjjzje) yqndyjjzje,sum(yqndsjjzje) yqndsjjzje from uf_zyczbksr_xj where sjzxsr  = "+id+" ";
        try {
            rst.executeQuery(sql);
            if (rst.getCounts()>0){
                rst.next();
                String yjsr = rst.getString("yjsr");
                String yqndyjjzje = rst.getString("yqndyjjzje");
                String yqndsjjzje = rst.getString("yqndsjjzje");
                String upSql = " update uf_zyczbksr_xj set yjsr = "+yjsr+",yqndyjjzje = "+yqndyjjzje+",yqndsjjzje = "+yqndsjjzje+"," +
                        " yjsrsy = "+yjsr+",jzsyje = "+yqndsjjzje+" where id = "+id+"  ";
                boolean isSuccess = rst.executeUpdate(upSql);
                if (isSuccess){
                    String xmid = getxmidbyid(id);
                    if (!xmid.isEmpty()){

                        boolean success = false;
                        rst.executeQuery(" select sum(yjsr) yjsr,sum(yqndyjjzje) yqndyjjzje,sum(yqndsjjzje) yqndsjjzje,sum(yjsrsy) yjsrsy,sum(yqndyjjzsyje) yqndyjjzsyje,sum(yqndsjjzsyje) yqndsjjzsyje " +
                                " from uf_zyczbksr_xj where sjzxsr  = "+xmid+" " );
                        if (rst.getCounts()>0){ //金额添加到上级
                            rst.next();
                            String addxjjesql = TreeStuctureDBUtil.SynUpperStrataSql(xmid, rst.getString("yjsr"), rst.getString("yqndyjjzje"), rst.getString("yqndsjjzje"),
                                    rst.getString("yjsrsy"), rst.getString("yqndyjjzsyje"), rst.getString("yqndsjjzsyje"));
                            success = rst.executeUpdate(addxjjesql);
                        }else{
                            success = true;
                        }

//                        boolean success = SynUpperStrata(xmid);

                        if (success){
                            rst.commit();
                            return true;
                        }else{
                            rst.rollback();
                            return false;
                        }
                    }else{
                        rst.commit();
                        return true;
                    }
                }else{
                    rst.rollback();
                    return false;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            rst.rollback();
        }
        rst.commit();
        return true;
    }

    /**
     * 添加上级金额sql
     * @param yjsr
     * @param yqndyjjzje
     * @param yqndsjjzje
     * @param yjsrsy
     * @param sjIds
     * @return
     */
    public static String addSjJeSql(String yjsr,String yqndyjjzje,String yqndsjjzje,String yjsrsy,String sjIds){
        String upSql = " update uf_zyczbksr_xj set " +
                "yjsr = yjsr+"+yjsr+",yqndyjjzje = yqndyjjzje+"+yqndyjjzje+",yqndsjjzje = yqndsjjzje+"+yqndsjjzje+"," +
                " yjsrsy = yjsrsy+"+yjsrsy+",yqndyjjzsyje = yqndyjjzsyje+"+yqndyjjzje+",yqndsjjzsyje = yqndsjjzsyje+"+yqndsjjzje+" where id in ("+sjIds+")  ";
        return upSql;
    }


    /**
     * 同步上层sql
     * @param id
     * @return
     */
    public static String SynUpperStrataSql(String id,String yjsr,String yqndyjjzje,String yqndsjjzje,String yjsrsy,String yqndyjjzsyje,String yqndsjjzsyje){
        String upSql = " update uf_zyczbksr_xj set yjsr = "+yjsr+",yqndyjjzje = "+yqndyjjzje+",yqndsjjzje = "+yqndsjjzje+"," +
                " yjsrsy = "+yjsrsy+",yqndyjjzsyje = "+yqndyjjzsyje+",yqndsjjzsyje = "+yqndsjjzsyje+" where id = "+id+"  ";
        return upSql;
    }

    /** add 11.30
     * 同步上层sql
     * @param id
     * @param rs
     * @return
     * @Version 2.0
     */
    public static String SynUpperStrataSql2(String id, RecordSetTrans rs){
        String yjsr = rs.getString("yjsr");
        String yqndyjjzje = rs.getString("yqndyjjzje");
        String yqndsjjzje = rs.getString("yqndsjjzje");
        String yjsrsy = rs.getString("yjsrsy");
        String yqndyjjzsyje = rs.getString("yqndyjjzsyje");
        String yqndsjjzsyje = rs.getString("yqndsjjzsyje");
        String yhpje = Util.null2String(rs.getString("yhpje")).equals("")?"0":rs.getString("yhpje");
        String djje = Util.null2String(rs.getString("djje")).equals("")?"0":rs.getString("djje");
        String sjbxje = Util.null2String(rs.getString("sjbxje")).equals("")?"0":rs.getString("sjbxje");
        String yhpsy = Util.null2String(rs.getString("yhpsy")).equals("")?"0":rs.getString("yhpsy");
        String yqndyjjzyyje = Util.null2String(rs.getString("yqndyjjzyyje")).equals("")?"0":rs.getString("yqndyjjzyyje");
        String jzyyje = Util.null2String(rs.getString("jzyyje")).equals("")?"0":rs.getString("jzyyje");
        String jzsyje = Util.null2String(rs.getString("jzsyje")).equals("")?"0":rs.getString("jzsyje");
        String yqndsjjzyyje = Util.null2String(rs.getString("yqndsjjzyyje")).equals("")?"0":rs.getString("yqndsjjzyyje");
        String snjzje = Util.null2String(rs.getString("snjzje")).equals("")?"0":rs.getString("snjzje");
        String snjzhpje = Util.null2String(rs.getString("snjzhpje")).equals("")?"0":rs.getString("snjzhpje");
        String snjzhpbxje = Util.null2String(rs.getString("snjzhpbxje")).equals("")?"0":rs.getString("snjzhpbxje");
        String snjzhpsyje = Util.null2String(rs.getString("snjzhpsyje")).equals("")?"0":rs.getString("snjzhpsyje");
        String snjzhpdjje = Util.null2String(rs.getString("snjzhpdjje")).equals("")?"0":rs.getString("snjzhpdjje");
        String upSql = " update uf_zyczbksr_xj set yjsr = "+yjsr+",yqndyjjzje = "+yqndyjjzje+",yqndsjjzje = "+yqndsjjzje+"," +
                " yjsrsy = "+yjsrsy+",yqndyjjzsyje = "+yqndyjjzsyje+",yqndsjjzsyje = "+yqndsjjzsyje+"," +
                " yhpje = "+yhpje+",djje = "+djje+",sjbxje = "+sjbxje+",yhpsy = "+yhpsy+",yqndyjjzyyje = "+yqndyjjzyyje+"," +
                "jzyyje = "+jzyyje+",jzsyje = "+jzsyje+",yqndsjjzyyje = "+yqndsjjzyyje+",snjzje = "+snjzje+",snjzhpje = "+snjzhpje+"," +
                "snjzhpbxje = "+snjzhpbxje+",snjzhpsyje = "+snjzhpsyje+",snjzhpdjje = "+snjzhpdjje+"  where id = "+id+"  ";
        return upSql;
    }

    /**
     * 修改所有上层sql
     * @param ids
     * @return
     */
    public static String upSjSql(Map<String,Object> param,String ids){
        String upSql = " update uf_zyczbksr_xj set yjsr = "+param.get("yjsr")+",yqndyjjzje = "+param.get("yqndyjjzje")+",yqndsjjzje = "+param.get("yqndsjjzje")+"," +
                " yjsrsy = "+param.get("yjsrsy")+",yqndyjjzsyje = "+param.get("yqndyjjzsyje")+",yqndsjjzsyje = "+param.get("yqndsjjzsyje")+" where id in ("+ids+")  ";
        return upSql;
    }

    /**
     * 修改科目级sql
     * @param id
     * @return
     */
    public static String upKmSql(Map<String,Object> param,String id){
        String upSql = " update uf_zyczbksr_km set yjsr = "+param.get("yjsr")+",yqndyjjzje = "+param.get("yqndyjjzje")+",yqndsjjzje = "+param.get("yqndsjjzje")+"," +
                " yjsrsy = "+param.get("yjsrsy")+",yqndyjjzsyje = "+param.get("yqndyjjzsyje")+",yqndsjjzsyje = "+param.get("yqndsjjzsyje")+" where id = "+id+"  ";
        return upSql;
    }

    public static String getxmidbyid(String id){
        RecordSet rs = new RecordSet();
        String sql = " select sjzxsr from uf_zyczbksr_xj where id = "+id+" ";
        rs.executeQuery(sql);
        rs.next();
        return rs.getString("sjzxsr");
    }

    /**
     * 获取二级汇总信息
     * @param id
     * @return
     */
    public static String getEjSumJe(String id){
        RecordSet rs = new RecordSet();
        String sql = " select sum(yjsr) yjsr,sum(yqndyjjzje) yqndyjjzje,sum(yqndsjjzje) yqndsjjzje from uf_zyczbksr_xj sjzxsr where szgnkm = "+id+" ";
        return sql;
    }

    /**
     * 获取最底层金额汇总
     * @param id
     * @return
     */
    public static String getbottomSumJe(String id){
        RecordSet rs = new RecordSet();
        String sql = " select sum(yjsr) yjsr,sum(yqndyjjzje) yqndyjjzje,sum(yqndsjjzje) yqndsjjzje from uf_zyczbksr_xj sjzxsr where sjzxsr = "+id+" ";
        return sql;
    }

    /**
     * 覆盖中央收入科目
     * @param param
     * @return
     */
    public static boolean upfzysr_km(Map<String,Object> param,String id){
        RecordSet rs = new RecordSet();
        String[] names = param.keySet().toArray(new String[param.size()]);
        Object[] values = param.values().toArray();

        String sql = upzysr_kmSql("uf_zyczbksr_km", names, values, id);
        boolean isSuccess = rs.executeUpdate(sql);
        return isSuccess;
    }

    /**
     * 拼接sql
     * @param tbName
     * @param name
     * @param values
     * @param id
     * @return
     */
    public static String upzysr_kmSql(String tbName, String[] name, Object[] values, String id){
        StringBuffer sb = new StringBuffer();
        sb.append(" update "+tbName+" set ");
        for (int i = 0; i < name.length; i++) {
            if (values[i].equals(""))
                continue;

            sb.append(" "+name[i]+" = '"+values[i]+"',");
        }
        return sb.substring(0,sb.length() - 1).toString() + " where id = "+id+" ";
    }

    //------------------------------------------------------------------------------展示树结构

    public static List<TreeStucture> getchildrenKm(Map<String,List<TreeStucture>> map, String sjid){
        for (Map.Entry<String, List<TreeStucture>> entry :map.entrySet()) {
            if (entry.getKey().equals(sjid)){
                return entry.getValue();
            }
        }
        return null;
    }

    public static String querykmmcById(String kmdm){
        RecordSet rs = new RecordSet();
        String sql = " select kmmc from uf_gnkm where kmdm = "+kmdm+" ";
        rs.next();
        return rs.getString("kmmc");
    }

    public static List<TreeStucture> children(Integer id,Integer ix,String tableNameXj){ //二级
        List<TreeStucture>  listtree = new ArrayList<>();
        RecordSet rs = new RecordSet();
        String sql = " select * from "+tableNameXj+" where sjzxsr is null and szgnkm = "+id+"  ";
        rs.executeQuery(sql);
        for (int i = 0; i < rs.getCounts() ; i ++){
            TreeStucture tree = new TreeStucture();
            rs.next();
            tree.setTitle(rs.getString("srzx"));
            String key = "0-0-"+ix+"-"+i;
            tree.setKey(key);
            List<TreeStucture> list = TreeStuctureDBUtil.queryXj(rs.getInt("id"), id, key,tableNameXj);
            if (list.isEmpty()){
                tree.setLeaf(false);
            }else{
                tree.setChildren(list);
                tree.setLeaf(true);
            }
            tree.setSrnf(rs.getString("srnf"));
            String xmid = rs.getString("id");
            tree.setId(xmid);
            tree.setYqndsjjzje(rs.getString("yqndsjjzje"));
            tree.setYqndyjjzje(rs.getString("yqndyjjzje"));
            tree.setYjsr(rs.getString("yjsr"));
            tree.setYjId(rs.getString("szgnkm"));
            tree.setSjId(rs.getString("szgnkm"));
            tree.setYjsrsy(rs.getString("yjsrsy"));
            tree.setYqndyjjzsyje(rs.getString("yqndyjjzsyje"));
            tree.setYqndsjjzsyje(rs.getString("yqndsjjzsyje"));
            tree.setXmdm(rs.getString("xmdm"));

            tree.setGlh(rs.getString("glh"));

            RecordSet rss = getkmById(xmid);
                tree.setKmdm(rss.getString("kmdm"));
                tree.setKmmc(rss.getString("gnkmmc"));
                String dwid = rss.getString("dw");
                RecordSet dw = rmcd.getSBDWById(Integer.valueOf(dwid));
                dw.next();
                String dwStr = dw.getString("SUBCOMPANYNAME");
                tree.setDwId(dwid);
                tree.setDw(dwStr);
                tree.setSrnf(rss.getString("srnf"));

            listtree.add(tree);
        }
        return listtree;
    }

    public static RecordSet getkmById(String id){
        RecordSet rs = new RecordSet();
        String sql  = " select kmdm,gnkmmc,srnf,dw from uf_zyczbksr_km where id = (select szgnkm from uf_zyczbksr_xj where id = "+id+")  ";
        rs.executeQuery(sql);
        rs.next();
        return rs;
    }

    private static PropertyManagementCostDao rmcd=new PropertyManagementCostDao();

    public static List<TreeStucture> queryXj(Integer sjzxsr, Integer szgnkm, String key,String tableNameXj){ //所有下级 0-0-0
        List<TreeStucture>  list = new ArrayList<>();
        RecordSet rs = new RecordSet();
        String sql = " select * from "+tableNameXj+" where sjzxsr = "+sjzxsr+" and szgnkm = "+szgnkm+"  ";
        rs.executeQuery(sql);
        if (rs.getCounts()>0){
            int i = 0;
            while (rs.next()){
                TreeStucture tree = new TreeStucture();
                tree.setTitle(rs.getString("srzx"));
                String paramKey = key+"-"+i;
                tree.setKey(key+"-"+i);
                List<TreeStucture> trees = TreeStuctureDBUtil.queryXj(rs.getInt("id"), szgnkm, paramKey,tableNameXj);
                if (trees.isEmpty()){
                    tree.setLeaf(false);
                }else{
                    tree.setLeaf(true);
                    tree.setChildren(trees);
                }
                tree.setSrnf(rs.getString("srnf"));
                String id = rs.getString("id");
                tree.setId(id);
                tree.setYjsr(rs.getString("yjsr"));
                tree.setYqndsjjzje(rs.getString("yqndsjjzje"));
                tree.setYqndyjjzje(rs.getString("yqndyjjzje"));
                tree.setYjId(rs.getString("szgnkm"));
                tree.setSjId(rs.getString("sjzxsr"));
                tree.setXmdm(rs.getString("xmdm"));
                tree.setGlh(rs.getString("glh"));
                tree.setYjsrsy(rs.getString("yjsrsy"));
                tree.setYqndyjjzsyje(rs.getString("yqndyjjzsyje"));
                tree.setYqndsjjzsyje(rs.getString("yqndsjjzsyje"));

                RecordSet rss = getkmById(id);
                    tree.setKmdm(rss.getString("kmdm"));
                    tree.setKmmc(rss.getString("gnkmmc"));
                    String dwid = rss.getString("dw");
                    RecordSet dw = rmcd.getSBDWById(Integer.valueOf(dwid));
                    dw.next();
                    String dwStr = dw.getString("SUBCOMPANYNAME");
                    tree.setDwId(dwid);
                    tree.setDw(dwStr);
                    tree.setSrnf(rss.getString("srnf"));
                list.add(tree);
                i++;
            }
            return list;
        }else{
            return list;
        }
    }

    //--------------------------------------------------------------------------------------------------------------删除


    /**
     * 补参数
     * @param param
     * @return
     */
    public static Map<String,Object> addParam(Map<String,Object> param){
        RecordSet rs = new RecordSet();
        String sql = "select yjsr,yqndyjjzje,yqndsjjzje from uf_zyczbksr_xj where id = "+param.get("id")+" ";
        rs.executeQuery(sql);
        while (rs.next()){
            param.put("yjsr",rs.getString("yjsr"));
            param.put("yqndyjjzje",rs.getString("yqndyjjzje"));
            param.put("yqndsjjzje",rs.getString("yqndsjjzje"));
        }
        return param;
    }

    /**
     * 删除下级
     * @param tmpParam
     * @return
     */
    public static boolean deleteXj(Map<String,Object> tmpParam){
        Map<String, Object> param = addParam(tmpParam);
        boolean dtSuccess = false;
        boolean upSuccess = false;
        boolean kmupSuccess = false;
        boolean tztdSuccess = false;
        RecordSetTrans rs = new RecordSetTrans();
        rs.setAutoCommit(false);
        try {
            if (param.containsKey("sjzxsr")){ //sjzxsr 存在 那么属于二级以下
                List<String> superiorId = TreeStuctureDBUtil.getSuperiorId((Integer) param.get("sjzxsr"));
//                String sql = " update uf_zyczbksr_xj set yjsr = yjsr - "+param.get("yjsr")+" where id in ("+ Joiner.on(",").join((Iterable<?>) superiorId)+") ";
                String sql = dtXj(superiorId, param);
                upSuccess = rs.executeUpdate(sql);
            }else{
                upSuccess = true;
            }
            List<String> idlist = getXj(param.get("id").toString());
            idlist.add(param.get("id").toString());
            String join = Joiner.on(",").join((Iterable<?>) idlist);
            String dtsql = " delete uf_zyczbksr_xj where id in ("+join+") ";
            String tzsql = " delete uf_zyczbksrtz where dzdyzxsr in ("+join+") ";

//            String kmupsql = " update uf_zyczbksr_km set yjsr = yjsr - "+param.get("yjsr")+" where id = "+param.get("szgnkm")+" ";
            String kmupsql = dtKm((Integer) param.get("szgnkm"), param);
            dtSuccess = rs.executeUpdate(dtsql);
            kmupSuccess = rs.executeUpdate(kmupsql);
            tztdSuccess = rs.executeUpdate(tzsql);
        } catch (Exception e) {
            e.printStackTrace();
            rs.rollback();
            return false;
        }
        if (dtSuccess&&upSuccess&&kmupSuccess&&tztdSuccess){
            rs.commit();
            return true;
        }else{
            rs.rollback();
            return false;
        }
    }

    public static boolean deleteXj2(Map<String,Object> param){
        param.remove("grade");
        String id = param.get("id").toString();

        if (!isDelete(id,"uf_zyczbksr_xj")){
            return false;
        }


        String dtsql = " delete uf_zyczbksr_xj where id ="+id+" ";
        LOGGER.info("删除中央下级sql----："+dtsql);
        RecordSetTrans rs = new RecordSetTrans();
        rs.setAutoCommit(false);
        try {
            if (rs.executeUpdate(dtsql)){
                boolean IsSuccessKm = false;
                boolean IsSuccessXj = false;
                if(param.containsKey("sjzxsr")){
                    rs.executeQuery(" select sum(yjsr) yjsr,sum(yqndyjjzje) yqndyjjzje,sum(yqndsjjzje) yqndsjjzje,sum(yjsrsy) yjsrsy,sum(yqndyjjzsyje) yqndyjjzsyje,sum(yqndsjjzsyje) yqndsjjzsyje " +
                            " from uf_zyczbksr_xj where sjzxsr  = "+param.get("sjzxsr").toString()+" " );
                    rs.next();
                    if (!rs.getString("yjsr").equals("")){
                        String addxjjesql = TreeStuctureDBUtil.SynUpperStrataSql(param.get("sjzxsr").toString(), rs.getString("yjsr"), rs.getString("yqndyjjzje"), rs.getString("yqndsjjzje"),
                                rs.getString("yjsrsy"), rs.getString("yqndyjjzsyje"), rs.getString("yqndsjjzsyje"));
                        LOGGER.info("删除中央下级时 同步到上级sql----："+addxjjesql);
                        IsSuccessXj = rs.executeUpdate(addxjjesql);
                    }else{
                        String addxjjesql = TreeStuctureDBUtil.SynUpperStrataSql(param.get("sjzxsr").toString(), "0", "0", "0",
                               "0", "0", "0");
                        LOGGER.info("删除中央下级时 同步到上级sql----："+addxjjesql);
                        IsSuccessXj = rs.executeUpdate(addxjjesql);
                    }
//                        boolean addxjjesql = TreeStuctureDBUtil.SynUpperStrata(param.get("sjzxsr").toString());//金额添加到上级

                    String addkmjesql = TreeStuctureDBUtil.AddKmJeSql((Integer) param.get("szgnkm"),param);//金额添加到一级
                    LOGGER.info("删除中央下级时 同步到科目级sql----："+addkmjesql);

                    IsSuccessKm = rs.executeUpdate(addkmjesql);
                }else{
                    String addkmjesql = TreeStuctureDBUtil.AddKmJeSql((Integer) param.get("szgnkm"),param);//金额添加到一级
                    LOGGER.info("删除中央下级时 同步到科目级sql----："+addkmjesql);
                    IsSuccessKm = rs.executeUpdate(addkmjesql);
                    IsSuccessXj = true;
                }
                if (IsSuccessKm&&IsSuccessXj){
                    rs.commit();
                    return true;
                }else{
                    rs.rollback();
                    return false;
                }
            }
        } catch (Exception e) {
            rs.rollback();
            e.printStackTrace();
        }

        return false;
    }

    public static boolean isDelete(String id,String tab){
        RecordSet rs = new RecordSet();
        String sql = " select yhpje from "+tab+"  where id = "+id+" ";
        rs.executeQuery(sql);
        rs.next();
        if (rs.getDouble("yhpje")>0){
            return false;
        }else{
            return true;
        }
    }

    /**
     * 扣减一级
     * @param szgnkm
     * @param param
     * @return
     */
    public static String dtKm(Integer szgnkm,Map<String,Object> param){
        StringBuffer sb = new StringBuffer();

        sb.append(" update uf_zyczbksr_km set ");
        if (param.containsKey("yjsr"))
            sb.append("yjsr = yjsr-"+param.get("yjsr")+",yjsrsy = yjsrsy-"+param.get("yjsr")+",");
        if (param.containsKey("yqndyjjzje"))
            sb.append("yqndyjjzje = yqndyjjzje-"+param.get("yqndyjjzje")+",yqndyjjzsyje = yqndyjjzsyje- "+param.get("yqndyjjzje")+",");
        if (param.containsKey("yqndsjjzje"))
            sb.append("yqndsjjzje = yqndsjjzje-"+param.get("yqndsjjzje")+",yqndsjjzsyje = yqndsjjzsyje - "+param.get("yqndsjjzje")+",");


//        String sql = " update uf_zyczbksr_km set yjsr = yjsr+" + je + " where id = " + szgnkm + " ";
        return sb.substring(0,sb.length() - 1).toString() + " where id = " + szgnkm + " ";
    }

    /**
     * 扣减上级
     * @param superiorId
     * @param param
     * @return
     */
    public static String dtXj(List<String> superiorId,Map<String,Object> param){
        StringBuffer sb = new StringBuffer();
        sb.append(" update uf_zyczbksr_xj set ");
        if (param.containsKey("yjsr"))
            sb.append("yjsr = yjsr-"+param.get("yjsr")+",yjsrsy = yjsrsy - "+param.get("yjsr")+",");
        if (param.containsKey("yqndyjjzje"))
            sb.append("yqndyjjzje = yqndyjjzje-"+param.get("yqndyjjzje")+",yqndyjjzsyje = yqndyjjzsyje - "+param.get("yqndyjjzje")+",");
        if (param.containsKey("yqndsjjzje"))
            sb.append("yqndsjjzje = yqndsjjzje-"+param.get("yqndsjjzje")+",yqndsjjzsyje = yqndsjjzsyje - "+param.get("yqndsjjzje")+",");

        return sb.substring(0,sb.length() - 1).toString() + " where id in ("+Joiner.on(",").join((Iterable<?>) superiorId)+") ";
    }

    /**
     * 删除一级
     * @param id
     * @return
     */
    public  static boolean deleteKm(String id){
        if (!isDelete(id,"uf_zyczbksr_km")){
            return false;
        }

        RecordSetTrans rs = new RecordSetTrans();
        rs.setAutoCommit(false);
        String kmsql = " delete uf_zyczbksr_km where id = "+id+" ";
        String xjsql = " delete uf_zyczbksr_xj where szgnkm = "+id+" ";
        boolean kmSuccess = false;
        boolean xjSuccess = false;
        try {
            kmSuccess = rs.executeUpdate(kmsql);
            xjSuccess = rs.executeUpdate(xjsql);
        } catch (Exception e) {
            e.printStackTrace();
            rs.rollback();
        }

        if (kmSuccess&&xjSuccess){
            rs.commit();
            return true;
        }else{
            rs.rollback();
            return false;
        }
    }

    //----------------------------------------------------------------------------------------------------------------------------------------修改

    /**
     * 修改中央科目级
     * @param param
     * @param uf_zyczbksr_km
     * @return
     */
    public static boolean modifyzysr_km(Map<String,Object> param,String uf_zyczbksr_km){
        param.remove("grade");
        String id = param.get("id").toString();
        param.remove("id");

        boolean yjsr = true;
        boolean yqndyjjzje = true;
        boolean yqndsjjzje = true;

        if (param.containsKey("yjsr")){
            yjsr = IsVczy_km(id, param.get("yjsr").toString(), "yhpje");
//            param.put("yjsrsy","yjsrsy - "+param.get("yjsr"));
            param.put("yjsrsy",param.get("yjsr")+ " - yhpje ");
        }
        if (param.containsKey("yqndyjjzje")){
            yqndyjjzje = IsVczy_km(id, param.get("yqndyjjzje").toString(), "yqndyjjzyyje");
//            param.put("yqndyjjzsyje","yqndyjjzsyje - "+param.get("yqndyjjzje"));
            param.put("yqndyjjzsyje",param.get("yqndyjjzje")+ " - yqndyjjzyyje");
        }
        if (param.containsKey("yqndsjjzje")){
            yqndsjjzje = IsVczy_km(id, param.get("yqndsjjzje").toString(), "yqndsjjzyyje");
//            param.put("yqndsjjzsyje","yqndsjjzsyje - "+param.get("yqndsjjzje"));
            param.put("yqndsjjzsyje",param.get("yqndsjjzje")+" - yqndsjjzyyje ");
        }

        if (yjsr && yqndyjjzje && yqndsjjzje){
            String[] name = param.keySet().toArray(new String[param.size()]);
            Object[] values = param.values().toArray();

            String sql = NonCentralTreeDBUtil.modifySql(uf_zyczbksr_km, name, values, id);
            LOGGER.info("修改中央科目级sql----："+sql);
            RecordSet rs = new RecordSet();
            return rs.executeUpdate(sql);
        }else{
            return false;
        }
    }

    //up 21.11.30
//    /**
//     * 修改中央下级
//     * @param param
//     * @param uf_zyczbksr_xj
//     * @return
//     */
//    public static boolean modifyzysr_xj(Map<String,Object> param,String uf_zyczbksr_xj){
//        param.remove("grade");
//        String id = param.get("id").toString();
//        param.remove("id");
//
//        boolean yjsr = true;
//        boolean yqndyjjzje = true;
//        boolean yqndsjjzje = true;
//
//        if (param.containsKey("yjsr")){
//            yjsr = IsVczy_xj(id, param.get("yjsr").toString(), "yhpje");
////            param.put("yjsrsy","yjsrsy - "+param.get("yjsr"));
//            param.put("yjsrsy",param.get("yjsr")+ " - yhpje ");
//        }
//        if (param.containsKey("yqndyjjzje")){
//            yqndyjjzje = IsVczy_xj(id, param.get("yqndyjjzje").toString(), "yqndyjjzyyje");
////            param.put("yqndyjjzsyje","yqndyjjzsyje - "+param.get("yqndyjjzje"));
//            param.put("yqndyjjzsyje",param.get("yqndyjjzje")+ " - yqndyjjzyyje");
//        }
//        if (param.containsKey("yqndsjjzje")){
//            yqndsjjzje = IsVczy_xj(id, param.get("yqndsjjzje").toString(), "yqndsjjzyyje");
////            param.put("yqndsjjzsyje","yqndsjjzsyje - "+param.get("yqndsjjzje"));
//            param.put("yqndsjjzsyje",param.get("yqndsjjzje")+" - yqndsjjzyyje ");
//        }
//
//        if (yjsr && yqndyjjzje && yqndsjjzje){
//            String[] name = param.keySet().toArray(new String[param.size()]);
//            Object[] values = param.values().toArray();
//
//            String sql = NonCentralTreeDBUtil.modifySql(uf_zyczbksr_xj, name, values, id);
//            LOGGER.info("修改中央下级sql----："+sql);
//            RecordSetTrans rs = new RecordSetTrans();
//            try {
//                if (rs.executeUpdate(sql)){
//                    boolean IsSuccessKm = false;
//                    boolean IsSuccessXj = false;
//                    if(param.containsKey("sjzxsr")){
//                        rs.executeQuery(" select sum(yjsr) yjsr,sum(yqndyjjzje) yqndyjjzje,sum(yqndsjjzje) yqndsjjzje,sum(yjsrsy) yjsrsy,sum(yqndyjjzsyje) yqndyjjzsyje,sum(yqndsjjzsyje) yqndsjjzsyje " +
//                                " from uf_zyczbksr_xj where sjzxsr  = "+param.get("sjzxsr").toString()+" " );
//                        if (rs.getCounts()>0){
//                            rs.next();
//                            String addxjjesql = TreeStuctureDBUtil.SynUpperStrataSql(param.get("sjzxsr").toString(), rs.getString("yjsr"), rs.getString("yqndyjjzje"), rs.getString("yqndsjjzje"),
//                                    rs.getString("yjsrsy"), rs.getString("yqndyjjzsyje"), rs.getString("yqndsjjzsyje"));
//                            LOGGER.info("修改中央下级时 同步到上级sql----："+addxjjesql);
//                            IsSuccessXj = rs.executeUpdate(addxjjesql);
//                        }else{
//                            IsSuccessXj = true;
//                        }
//
////                        boolean addxjjesql = TreeStuctureDBUtil.SynUpperStrata(param.get("sjzxsr").toString());//金额添加到上级
//
//                        String addkmjesql = TreeStuctureDBUtil.AddKmJeSql((Integer) param.get("szgnkm"),param);//金额添加到一级
//                        LOGGER.info("修改中央下级时 同步到科目级sql----："+addkmjesql);
//
//                        IsSuccessKm = rs.executeUpdate(addkmjesql);
//                    }else{
//                        String addkmjesql = TreeStuctureDBUtil.AddKmJeSql((Integer) param.get("szgnkm"),param);//金额添加到一级
//                        LOGGER.info("修改中央下级时 同步到科目级sql----："+addkmjesql);
//                        IsSuccessKm = rs.executeUpdate(addkmjesql);
//                        IsSuccessXj = true;
//                    }
//                    if (IsSuccessKm&&IsSuccessXj){
//                        rs.commit();
//                        return true;
//                    }else{
//                        rs.rollback();
//                        return false;
//                    }
//                }
//            } catch (Exception e) {
//                rs.rollback();
//                e.printStackTrace();
//            }
//
//        }else{
//            return false;
//        }
//        return false;
//    }

    /**
     * 修改中央下级
     * @param param
     * @param uf_zyczbksr_xj
     * @return
     */
    public static boolean modifyzysr_xj(Map<String,Object> param,String uf_zyczbksr_xj){
        param.remove("grade");
        String id = param.get("id").toString();
        param.remove("id");

        boolean yjsr = true;
        boolean yqndyjjzje = true;
        boolean yqndsjjzje = true;

        if (param.containsKey("yjsr")){
            yjsr = IsVczy_xj(id, param.get("yjsr").toString(), "yhpje");
            String je = getCustomSqlVle("uf_zyczbksr_xj", "yjsr", id);
            param.put("yjsrsy","yjsrsy+("+param.get("yjsr")+ " - "+je+") ");
            param.put("yjsr"," yjsr +("+param.get("yjsr")+ " - "+je+" )");
        }
        if (param.containsKey("yqndyjjzje")){
            yqndyjjzje = IsVczy_xj(id, param.get("yqndyjjzje").toString(), "yqndyjjzyyje");
            String je = getCustomSqlVle("uf_zyczbksr_xj", "yqndyjjzje", id);
            param.put("yqndyjjzsyje","yqndyjjzsyje+("+param.get("yqndyjjzje")+ " - "+je+")");
            param.put("yqndyjjzje","yqndyjjzje+("+param.get("yqndyjjzje")+ " - "+je+")");
        }
        if (param.containsKey("yqndsjjzje")){
            yqndsjjzje = IsVczy_xj(id, param.get("yqndsjjzje").toString(), "yqndsjjzyyje");
            String je = getCustomSqlVle("uf_zyczbksr_xj", "yqndsjjzje", id);
            param.put("yqndsjjzsyje","yqndsjjzsyje+("+param.get("yqndsjjzje")+ " - "+je+")");
            param.put("yqndsjjzje","yqndsjjzje+("+param.get("yqndsjjzje")+ " - "+je+")");
        }

        if (yjsr && yqndyjjzje && yqndsjjzje){
            String[] name = param.keySet().toArray(new String[param.size()]);
            Object[] values = param.values().toArray();

            String sql = NonCentralTreeDBUtil.modifySql(uf_zyczbksr_xj, name, values, id);
            LOGGER.info("修改中央下级sql----："+sql);
            RecordSetTrans rs = new RecordSetTrans();
            rs.setAutoCommit(false);
            try {
                if (rs.executeUpdate(sql)){
                    boolean IsSuccessKm = false;
                    boolean IsSuccessXj = false;
                    if(param.containsKey("sjzxsr")){

                        List<String> superiorId = TreeStuctureDBUtil.getSuperiorId(Integer.parseInt(param.get("sjzxsr").toString()));
                        String sjIds = Joiner.on(",").join((Iterable<?>) superiorId);

                        String addxjjesql = TreeStuctureDBUtil.upSjSql(param, sjIds);
                        IsSuccessXj = rs.executeUpdate(addxjjesql);

                        String addkmjesql = TreeStuctureDBUtil.upKmSql(param, param.get("szgnkm").toString());
                        LOGGER.info("修改中央下级时 同步到科目级sql----："+addkmjesql);

                        IsSuccessKm = rs.executeUpdate(addkmjesql);
                    }else{
                        String addkmjesql = TreeStuctureDBUtil.upKmSql(param, param.get("szgnkm").toString());
                        LOGGER.info("修改中央下级时 同步到科目级sql----："+addkmjesql);
                        IsSuccessKm = rs.executeUpdate(addkmjesql);
                        IsSuccessXj = true;
                    }
                    if (IsSuccessKm&&IsSuccessXj){
                        rs.commit();
                        return true;
                    }else{
                        rs.rollback();
                        return false;
                    }
                }
            } catch (Exception e) {
                rs.rollback();
                e.printStackTrace();
                return false;
            }

        }else{
            return false;
        }
        return false;
    }

    /**
     * 校验修改时剩余金额是否足够更改
     * @param id
     * @param je
     * @param str
     * @return
     */
    public static boolean IsVczy_km(String id,String je,String str){
        RecordSet rs = new RecordSet();
        String sql = " select "+str+" from uf_zyczbksr_km where id = "+id+" ";
        rs.executeQuery(sql);
        rs.next();
        BigDecimal bg1 = new BigDecimal(je);
        BigDecimal bg2 = new BigDecimal(rs.getString(str));
        /*-1,表示bd1小于bd2；
        0,表示bd1等于bd2；
        1,表示bd1大于bd2；*/
        int i = bg1.compareTo(bg2);
        if (i == 1 || i == 0){
            return true;
        }else{
            return false;
        }
    }



    /**
     * 校验修改时剩余金额是否足够更改
     * @param id
     * @param je
     * @param str
     * @return
     */
    public static boolean IsVczy_xj(String id,String je,String str){
        RecordSet rs = new RecordSet();
        String sql = " select "+str+" from uf_zyczbksr_xj where id = "+id+" ";
        rs.executeQuery(sql);
        rs.next();
        BigDecimal bg1 = new BigDecimal(je);
        BigDecimal bg2 = new BigDecimal(rs.getString(str));
        /*-1,表示bd1小于bd2；
        0,表示bd1等于bd2；
        1,表示bd1大于bd2；*/
        int i = bg1.compareTo(bg2);
        if (i == 1 || i == 0){
            return true;
        }else{
            return false;
        }
    }

    /**
     * 根据参数取值
     * @param tableName
     * @param field
     * @param id
     * @return
     */
    public static String getCustomSqlVle(String tableName,String field,String id){
        RecordSet rs = new RecordSet();
        String sql = "select "+field+" from "+tableName+" where id = "+id+" ";
        rs.executeQuery(sql);
        rs.next();
        return Util.null2String(rs.getString(field));
    }


    /**
     * 修改下级
     * @param param
     * @return
     */
    public static boolean updateXj(Map<String,Object> param){
        RecordSetTrans rs = new RecordSetTrans();
        rs.setAutoCommit(false);

        boolean xjupSuccess = false;
        boolean kmupSuccess = false;
        boolean istIsSuccess = false;
        try {
            if (param.containsKey("sjzxsr")){ //sjzxsr 存在 那么属于二级以下
                List<String> list = TreeStuctureDBUtil.getSuperiorId((Integer) param.get("sjzxsr"));
//                list.add(param.get("id").toString());

                String upsql = " update uf_zyczbksr_xj set yjsr = yjsr +"+param.get("yjsr")+" where id = "+param.get("id")+" ";
                boolean isUP = rs.executeUpdate(upsql);

                if (isUP){
                    String SumSql = TreeStuctureDBUtil.getbottomSumJe(param.get("sjzxsr").toString());
                    String sql = TreeStuctureDBUtil.AddxjJeSql(list, param,SumSql);
                    xjupSuccess = rs.executeUpdate(sql);
                }

            }else{
                String upsql = " update uf_zyczbksr_xj set yjsr = yjsr +"+param.get("yjsr")+" where id = "+param.get("id")+" ";
                String sql = upEj(param.get("id").toString(), param);

                xjupSuccess = rs.executeUpdate(sql);
            }
//            String kmupsql = " update uf_zyczbksr_km set yjsr = yjsr + "+param.get("yjsr")+" where id = "+param.get("szgnkm")+" ";
            String kmupsql = TreeStuctureDBUtil.AddKmJeSql((Integer) param.get("szgnkm"), param);
            kmupSuccess = rs.executeUpdate(kmupsql);

            String istSql = " insert into uf_zyczbksrtz (dzdyzxsr,dzdygnkm,dzyy,dzrq,dzjey,mc) values" +
                    " ("+param.get("id")+","+param.get("szgnkm")+",'"+param.get("dzyy")+"','"+ Utils.getThisDate() +"',"+param.get("yjsr")+",'"+param.get("mc")+"')"; //增加到记录表

            istIsSuccess = rs.executeUpdate(istSql);
        } catch (Exception e) {
            e.printStackTrace();
            rs.rollback();
        }
        if (xjupSuccess&&kmupSuccess&&istIsSuccess){
            rs.commit();
            return true;
        }else{
            rs.rollback();
            return false;
        }
    }

    /**
     * 是否是最下层
     * @param id
     * @return
     */
    public static boolean IsBottomLayer(String id){
        RecordSet rs = new RecordSet();
        String sql = " select * from uf_zyczbksr_xj where sjzxsr = "+id+" ";
        rs.executeQuery(sql);
        int counts = rs.getCounts();
        if (rs.getCounts()<=0){
            return true;
        }else{
            return false;
        }
    }

    /**
     *修改下级2
     * @param param
     * @return
     */
    public static boolean updateXj2(Map<String,Object> param){
        RecordSetTrans rs = new RecordSetTrans();
        rs.setAutoCommit(false);

        boolean xjupSuccess = false;
        boolean kmupSuccess = false;
        boolean istIsSuccess = false;
        boolean is = IsBottomLayer(param.get("id").toString());
        try {
            if (is){
                String upsql = " update uf_zyczbksr_xj set yjsr = yjsr +"+param.get("yjsr")+" where id = "+param.get("id")+" ";
                boolean isup = rs.executeUpdate(upsql);
                if (isup){
                    if(param.containsKey("sjzxsr")){

                        rs.executeQuery(" select sum(yjsr) yjsr,sum(yqndyjjzje) yqndyjjzje,sum(yqndsjjzje) yqndsjjzje,sum(yjsrsy) yjsrsy,sum(yqndyjjzsyje) yqndyjjzsyje,sum(yqndsjjzsyje) yqndsjjzsyje " +
                                " from uf_zyczbksr_xj where sjzxsr  = "+param.get("sjzxsr").toString()+" " );
                        if (rs.getCounts()>0){ //金额添加到上级
                            rs.next();
                            String addxjjesql = TreeStuctureDBUtil.SynUpperStrataSql(param.get("sjzxsr").toString(), rs.getString("yjsr"), rs.getString("yqndyjjzje"), rs.getString("yqndsjjzje"),
                                    rs.getString("yjsrsy"), rs.getString("yqndyjjzsyje"), rs.getString("yqndsjjzsyje"));
                            xjupSuccess = rs.executeUpdate(addxjjesql);
                        }else{
                            xjupSuccess = true;
                        }

//                        boolean addxjjesql = TreeStuctureDBUtil.SynUpperStrata(param.get("sjzxsr").toString());//金额添加到上级
                        String addkmjesql = TreeStuctureDBUtil.AddKmJeSql((Integer) param.get("szgnkm"),param);//金额添加到一级
                        kmupSuccess = rs.executeUpdate(addkmjesql);
                    }else{
                        String addkmjesql = TreeStuctureDBUtil.AddKmJeSql((Integer) param.get("szgnkm"),param);//金额添加到一级
                        kmupSuccess = rs.executeUpdate(addkmjesql);
                        xjupSuccess = true;
                    }
                }
            }else{
                String upsql = " update uf_zyczbksr_xj set yjsr = yjsr +"+param.get("yjsr")+" where id = "+param.get("id")+" ";
                xjupSuccess = rs.executeUpdate(upsql);
                kmupSuccess = true;
            }

            String istSql = " insert into uf_zyczbksrtz (dzdyzxsr,dzdygnkm,dzyy,dzrq,dzjey,mc) values" +
                    " ("+param.get("id")+","+param.get("szgnkm")+",'"+param.get("dzyy")+"','"+ Utils.getThisDate() +"',"+param.get("yjsr")+",'"+param.get("mc")+"')"; //增加到记录表

            istIsSuccess = rs.executeUpdate(istSql);
        } catch (Exception e) {
            e.printStackTrace();
            rs.rollback();
        }
        if (xjupSuccess&&kmupSuccess&&istIsSuccess){
            rs.commit();
            return true;
        }else{
            rs.rollback();
            return false;
        }
    }

    /**
     * 修改二级
     * @param id
     * @param param
     * @return
     */
    public static String upEj(String id,Map<String,Object> param){
        StringBuffer sb = new StringBuffer();
        sb.append(" update uf_zyczbksr_xj set ");
        if (param.containsKey("yjsr"))
            sb.append("yjsr = yjsr+"+param.get("yjsr")+",yjsrsy = yjsrsy +"+param.get("yjsr")+",");
        if (param.containsKey("yqndyjjzje"))
            sb.append("yqndyjjzje = yqndyjjzje+"+param.get("yqndyjjzje")+",");
        if (param.containsKey("yqndsjjzje"))
            sb.append("yqndsjjzje = yqndsjjzje+"+param.get("yqndsjjzje")+",jzsyje = jzsyje+"+param.get("yqndsjjzje")+",");

        return sb.substring(0,sb.length() - 1).toString() + " where id = "+id+" ";
    }


    /**
     * 修改科目表
     * @param param
     * @return
     */
    public static boolean updateKm(Map<String,Object> param){
        RecordSetTrans rs = new RecordSetTrans();
        rs.setAutoCommit(false);
        StringBuffer sb = new StringBuffer();
        String sql = " update uf_zyczbksr_km set yjsr = yjsr+ "+param.get("yjsr")+" where id = "+param.get("id")+"  ";

//        String sql = TreeStuctureDBUtil.AddKmJeSql((Integer) param.get("id"), param);

        String istSql = " insert into uf_zyczbksrtz (dzdygnkm,dzyy,dzrq,dzjey,mc) values" +
                " ("+param.get("id")+",'"+param.get("dzyy")+"','"+ Utils.getThisDate() +"',"+param.get("yjsr")+",'"+param.get("mc")+"')";
        boolean IsSuccess = false;
        boolean istIsSuccess = false;
        try {
            IsSuccess = rs.executeUpdate(sql);
            istIsSuccess = rs.executeUpdate(istSql);
        } catch (Exception e) {
            e.printStackTrace();
            rs.rollback();
        }
        if (IsSuccess&&istIsSuccess){
            rs.commit();
            return true;
        }else{
            rs.rollback();
            return false;
        }
    }

    //----------------------------------------------------------------------------------------------------------------------------------------查看日志

    /**
     * 根据id获取所有下级
     * @param id
     * @return
     */
    public static List<String> getXj(String id){
        RecordSet rs = new RecordSet();
        List<String> list = new ArrayList<>();
        String sql = " select id from uf_zyczbksr_xj where sjzxsr = "+id+" ";
        rs.executeQuery(sql);
        while (rs.next()){
            list.add(rs.getString("id"));
            List<String> lists = getXj(rs.getString("id"));
            if (!lists.isEmpty())
                list.addAll(lists);
        }
        return list;
    }
    //----------------------------------------------------------------------------------------------------------------------------------------通用

    /**
     * 递归获取所有上级id
     * @param id
     * @return
     */
    public static List<String> getSuperiorId(Integer id){
        List<String> list = new ArrayList<>();
        list.add(String.valueOf(id));

        RecordSet rs = new RecordSet();
        String sql = " select sjzxsr from  uf_zyczbksr_xj where id = "+id+" ";
        rs.executeQuery(sql);
        rs.next();
        if (StrUtil.isNotEmpty(rs.getString("sjzxsr"))){
            List<String> sjzxsr = getSuperiorId(rs.getInt("sjzxsr"));
            list.addAll(sjzxsr);
        }
        return list;
    }

    /**
     * 根据用户id获取分部id
     * @return
     */
    public static Integer getSubCompanyIdByUserId(Integer id){
        RecordSet rs = new RecordSet();
        String sql = " select subcompanyid1 from hrmResource where id = "+id+" ";
        rs.executeQuery(sql);
        rs.next();
        return rs.getInt("subcompanyid1");
    }

    /**
     * 根据用户id获取部门id
     * @return
     */
    public static String getDepartmentIdByUserId(Integer id){
        RecordSet rs = new RecordSet();
        String sql = " select departmentid from hrmResource where id = "+id+" ";
        rs.executeQuery(sql);
        rs.next();
        return rs.getString("departmentid");
    }

    /**
     * 根据用户id获取矩阵分部id
     * @return
     */
    public static List<String> getSubcompanyDefinedByUserId(Integer id){
        RecordSet rs = new RecordSet();
        String sql = " select * from \n" +
                "(SELECT subcomid,value\n" +
                "FROM(\n" +
                "  SELECT subcomid, dwyscwry = CONVERT(xml,'<root><v>' + REPLACE(CONVERT(varchar(200),dwyscwry), ',', '</v><v>') + '</v></root>') FROM HrmSubcompanyDefined\n" +
                ")A\n" +
                "OUTER APPLY(\n" +
                "  SELECT value = N.v.value('.', 'varchar(100)') FROM A.dwyscwry.nodes('/root/v') N(v)\n" +
                ")B\n" +
                ")HrmSubcompanyDefined2 where value = "+id+" ";
        LOGGER.info("根据用户id获取矩阵分部id-sql："+sql);
        rs.executeQuery(sql);
        List<String> list = new ArrayList<>();
        while (rs.next()){
            list.add(rs.getString("subcomid"));
        }
        return list;
    }

    /**
     * 根据用户id判断部门是否为领导部门，如果是则可以查询以下所有分部 则不能查看
     * @return
     */
    public static List<String> getSupSubcomIdByUserId(Integer id,String str){
        List<String> list = new ArrayList<>();
        RecordSet rs = new RecordSet();
        String sql = " select hd.subcompanyid1,hd.departmentname from hrmResource hr join HrmDepartment hd on hr.departmentid = hd.id where hr.id = "+id+" and hd.departmentname like '%"+str+"%' ";
        rs.executeQuery(sql);
        if (rs.getCounts()>0){
            rs.next();
            list.add(rs.getString("subcompanyid1"));
            String sql2 = " select id from HrmSubCompany where supsubcomid = "+rs.getInt("subcompanyid1")+" ";
            rs.executeQuery(sql2);
            while (rs.next()){
                list.add(rs.getString("id"));
            }
        }
        return list;
    }
}
